﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Business.Entity;
using System.Data.SqlClient;
using System.Data;

namespace Business.Dal
{
    public class MesajProvider : Provider<Mesaj>
    {
        public override bool Insert(Mesaj tObject)
        {
            bool returnValue = false;
            SqlCommand cmd = new SqlCommand("MesajEkle");
            cmd.Parameters.Add("GonderenID", SqlDbType.Int).Value = tObject.GonderenID;
            cmd.Parameters.Add("AlanID", SqlDbType.Int).Value = tObject.AlanID;
            cmd.Parameters.Add("IlanID", SqlDbType.Int).Value = tObject.IlanID;
            cmd.Parameters.Add("Baslik", SqlDbType.NVarChar).Value = tObject.Baslik;
            cmd.Parameters.Add("Mesaj", SqlDbType.NVarChar).Value = tObject._Mesaj;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            tObject.MesajID = Convert.ToInt32(cmd.ExecuteScalar());
            returnValue = true;
            return returnValue;

        }

        public override bool Update(Mesaj tObject)
        {
            bool returnValue = false;
            SqlCommand cmd = new SqlCommand("MesajGuncelle");
            
            cmd.Parameters.Add("MesajID", SqlDbType.Int).Value = tObject.MesajID;
            cmd.Parameters.Add("GonderenID", SqlDbType.Int).Value = tObject.GonderenID;
            cmd.Parameters.Add("AlanID", SqlDbType.Int).Value = tObject.AlanID;
            cmd.Parameters.Add("IlanID", SqlDbType.Int).Value = tObject.IlanID;
            cmd.Parameters.Add("Baslik", SqlDbType.NVarChar).Value = tObject.Baslik;
            cmd.Parameters.Add("Mesaj", SqlDbType.NVarChar).Value = tObject._Mesaj;
            cmd.Parameters.Add("Okundu", SqlDbType.Bit).Value = tObject.Okundu;
            cmd.Parameters.Add("GonderenSildi", SqlDbType.Bit).Value = tObject.GonderenSildi;
            cmd.Parameters.Add("AlanSildi", SqlDbType.Bit).Value = tObject.AlanSildi;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            returnValue = Convert.ToBoolean(cmd.ExecuteNonQuery());
            return returnValue;

        }

        public override bool Delete(int RecordID)
        {
            bool returnValue = false;
            SqlCommand cmd = new SqlCommand("MesajAlanSil");
            cmd.Parameters.Add("MesajID", SqlDbType.Int).Value = RecordID;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            returnValue = Convert.ToBoolean(cmd.ExecuteNonQuery());
            return returnValue;
        }

        public List<Mesaj> GetAllRows()
        {
            List<Mesaj> returnValue = new List<Mesaj>();
            SqlCommand cmd = new SqlCommand("MesajlarHepsiniGetir");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                Mesaj M = new Mesaj();
                M.MesajID = dr["MesajID"] is DBNull ? 0 : Convert.ToInt32(dr["MesajID"]);
                M.GonderenID = dr["GonderenID"] is DBNull ? 0 : Convert.ToInt32(dr["GonderenID"]);
                M.AlanID = dr["AlanID"] is DBNull ? 0 : Convert.ToInt32(dr["AlanID"]);
                M.IlanID = dr["IlanID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
                M.Baslik = dr["Baslik"] is DBNull ? String.Empty : dr["Baslik"].ToString();
                M.Baslik = dr["Mesaj"] is DBNull ? String.Empty : dr["Mesaj"].ToString();
                M.Okundu=dr["Okundu"] is DBNull ? Convert.ToBoolean(0) : Convert.ToBoolean(dr["Okundu"]);
                M.Okundu=dr["GonderenSildi"] is DBNull ? Convert.ToBoolean(0) : Convert.ToBoolean(dr["GonderenSildi"]);
                M.Okundu = dr["AlanSildi"] is DBNull ? Convert.ToBoolean(0) : Convert.ToBoolean(dr["AlanSildi"]);
                returnValue.Add(M);
                
            }
            return returnValue;

        }

        public List<Mesaj> MesajlarOkunmamislariGetir(int UyeID)
        {
            List<Mesaj> returnValue = new List<Mesaj>();
            SqlCommand cmd = new SqlCommand("MesajlarOkunmamislariGetir");
            cmd.Parameters.Add("@UyeID", SqlDbType.Int).Value = UyeID;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
           
            while (dr.Read())
            {
                Mesaj M = new Mesaj();
                M.MesajID = dr["MesajID"] is DBNull ? 0 : Convert.ToInt32(dr["MesajID"]);
                M.GonderenID = dr["GonderenID"] is DBNull ? 0 : Convert.ToInt32(dr["GonderenID"]);
                M.AlanID = dr["AlanID"] is DBNull ? 0 : Convert.ToInt32(dr["AlanID"]);
                M.IlanID = dr["IlanID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
                M.Baslik = dr["Baslik"] is DBNull ? String.Empty : dr["Baslik"].ToString();
                M.Baslik = dr["Mesaj"] is DBNull ? String.Empty : dr["Mesaj"].ToString();
                M.Okundu = dr["Okundu"] is DBNull ? Convert.ToBoolean(0) : Convert.ToBoolean(dr["Okundu"]);
                M.Okundu = dr["GonderenSildi"] is DBNull ? Convert.ToBoolean(0) : Convert.ToBoolean(dr["GonderenSildi"]);
                M.Okundu = dr["AlanSildi"] is DBNull ? Convert.ToBoolean(0) : Convert.ToBoolean(dr["AlanSildi"]);
                returnValue.Add(M);
                
            }
            return returnValue;
        }

        public List<Mesaj> MesajlarAlanınSilmedikleriniGetir(int UyeID)
        {
            List<Mesaj> returnValue = new List<Mesaj>();
            SqlCommand cmd = new SqlCommand("MesajlarAlanınSilmedikleriniGetir");
            cmd.Parameters.Add("@AlanID", SqlDbType.Int).Value = UyeID;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Close();
            Tools.Open();
            SqlDataReader dr;
            dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Mesaj M = new Mesaj();
                M.MesajID = dr["MesajID"] is DBNull ? 0 : Convert.ToInt32(dr["MesajID"]);
                M.GonderenID = dr["GonderenID"] is DBNull ? 0 : Convert.ToInt32(dr["GonderenID"]);
                M.AlanID = dr["AlanID"] is DBNull ? 0 : Convert.ToInt32(dr["AlanID"]);
                M.IlanID = dr["IlanID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
                M.Baslik = dr["Baslik"] is DBNull ? String.Empty : dr["Baslik"].ToString();
                M.Baslik = dr["Mesaj"] is DBNull ? String.Empty : dr["Mesaj"].ToString();
                M.Okundu = dr["Okundu"] is DBNull ? Convert.ToBoolean(0) : Convert.ToBoolean(dr["Okundu"]);
                M.Gonderen = dr["Gonderen"] is DBNull ? String.Empty : dr["Gonderen"].ToString();
                M.Alan = dr["Alan"] is DBNull ? String.Empty : dr["Alan"].ToString();
                returnValue.Add(M);

            }
            dr.Close();
            return returnValue;
        }

        public List<Mesaj> MesajlarGetirAlanID(int AlanID)
        {
            List<Mesaj> returnValue=new List<Mesaj>();
            SqlCommand cmd = new SqlCommand("MesajlarGetirAlanID");
            cmd.Parameters.Add("AlanID",SqlDbType.Int).Value=AlanID;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                Mesaj M = new Mesaj();
                M.MesajID = dr["MesajID"] is DBNull ? 0 : Convert.ToInt32(dr["MesajID"]);
                M.GonderenID = dr["GonderenID"] is DBNull ? 0 : Convert.ToInt32(dr["GonderenID"]);
                M.AlanID = dr["AlanID"] is DBNull ? 0 : Convert.ToInt32(dr["AlanID"]);
                M.IlanID = dr["IlanID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
                M.Baslik = dr["Baslik"] is DBNull ? String.Empty : dr["Baslik"].ToString();
                M.Baslik = dr["Mesaj"] is DBNull ? String.Empty : dr["Mesaj"].ToString();
                M.Okundu = dr["Okundu"] is DBNull ? Convert.ToBoolean(0) : Convert.ToBoolean(dr["Okundu"]);
                M.Okundu = dr["GonderenSildi"] is DBNull ? Convert.ToBoolean(0) : Convert.ToBoolean(dr["GonderenSildi"]);
                M.Okundu = dr["AlanSildi"] is DBNull ? Convert.ToBoolean(0) : Convert.ToBoolean(dr["AlanSildi"]);
                returnValue.Add(M);
            }
            return returnValue;
        }

        public List<Mesaj> MesajlarGetirGonderenID(int GonderenID)
        {
            List<Mesaj> returnValue = new List<Mesaj>();
            SqlCommand cmd = new SqlCommand("MesajlarGetirGonderenID");
            cmd.Parameters.Add("GonderenID", SqlDbType.Int).Value = GonderenID;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            SqlDataReader dr;
            dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Mesaj M = new Mesaj();
                M.MesajID = dr["MesajID"] is DBNull ? 0 : Convert.ToInt32(dr["MesajID"]);
                M.GonderenID = dr["GonderenID"] is DBNull ? 0 : Convert.ToInt32(dr["GonderenID"]);
                M.AlanID = dr["AlanID"] is DBNull ? 0 : Convert.ToInt32(dr["AlanID"]);
                M.IlanID = dr["IlanID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
                M.Baslik = dr["Baslik"] is DBNull ? String.Empty : dr["Baslik"].ToString();
                M.Baslik = dr["Mesaj"] is DBNull ? String.Empty : dr["Mesaj"].ToString();
                M.Okundu = dr["Okundu"] is DBNull ? Convert.ToBoolean(0) : Convert.ToBoolean(dr["Okundu"]);
                M.Okundu = dr["GonderenSildi"] is DBNull ? Convert.ToBoolean(0) : Convert.ToBoolean(dr["GonderenSildi"]);
                M.Okundu = dr["AlanSildi"] is DBNull ? Convert.ToBoolean(0) : Convert.ToBoolean(dr["AlanSildi"]);
                returnValue.Add(M);
            }
            return returnValue;
        }

        public void MesajOkundu(int MesajID)
        {
            SqlCommand cmd = new SqlCommand("MesajOkundu");
            cmd.Parameters.Add("MesajID", SqlDbType.Int).Value = MesajID;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            cmd.ExecuteNonQuery();
        }

        public void MesajGonderenSil(int MesajID)
        {
            SqlCommand cmd = new SqlCommand("MesajGonderenSil");
            cmd.Parameters.Add("MesajID", SqlDbType.Int).Value = MesajID;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            cmd.ExecuteNonQuery();
        }

        public Mesaj MesajGetirByMesajID(int MesajID)
        {
            string mesaj = "";
            SqlCommand cmd = new SqlCommand("MesajGetirMesajID", Tools.Connection());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("MesajID", SqlDbType.Int).Value = MesajID;
            SqlDataReader dr;
            Tools.Open();
            dr = cmd.ExecuteReader();
            dr.Read();
            Mesaj m = new Mesaj();
            m._Mesaj = dr["Mesaj"] is DBNull ? String.Empty : dr["Mesaj"].ToString();
            m.MesajID = dr["MesajID"] is DBNull ? 0 : Convert.ToInt32(dr["MesajID"]);
            m.AlanID = dr["AlanID"] is DBNull ? 0 : Convert.ToInt32(dr["AlanID"]);
            m.IlanID = dr["IlanID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
            m.Baslik = dr["Baslik"] is DBNull ? String.Empty : dr["Baslik"].ToString();
            return m;
        }

        public string GonderenGetirByMesajID(int MesajID)
        {
            string adSoyad = "";
            SqlCommand cmd = new SqlCommand("MesajGonderenGetirByMesajID", Tools.Connection());
            cmd.Parameters.Add("@MesajID", SqlDbType.Int).Value = MesajID;
            SqlDataReader dr;
            Tools.Open();
            dr = cmd.ExecuteReader();
            dr.Read();
            adSoyad = dr[0].ToString();
            return adSoyad;
        }

        public string GonderenGetirByMesajID()
        {
            throw new NotImplementedException();
        }
    }
}
